﻿Imports System.Data
Imports System.Data.SqlClient

Public Class cMain

    Dim con As New SqlConnection(strcon)

    Public Function getData(ByVal sql As String) As DataTable
        Dim dt As New DataTable
        Dim da As New SqlDataAdapter(sql, strcon)
        Try
            da.Fill(dt)
        Catch ex As Exception
            da.Dispose()
        Finally
            da.Dispose()
        End Try
        Return dt
    End Function

    Public Function getMax(ByVal TableName As String, ByVal ColumnName As String) As String
        Dim st As String
        Dim cmd As New SqlCommand
        cmd.CommandText = "SELECT MAX(" & ColumnName & ") FROM " & TableName & ""
        cmd.Connection = con
        Try
            con.Open()
            st = cmd.ExecuteScalar()
        Catch ex As Exception
            st = ""
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return st
    End Function

    Public Function getMaxPlus(ByVal TableName As String, ByVal ColumnName As String, ByVal TextPlus As String) As String
        Dim st As String, num As Integer, LenNum As Integer, stID As String = ""
        Dim cmd As New SqlCommand
        cmd.CommandText = "SELECT MAX(" & ColumnName & ") FROM " & TableName & ""
        cmd.Connection = con
        Try
            con.Open()
            st = cmd.ExecuteScalar()
            num = CInt(Right(st, Len(st) - 3)) + 1
            LenNum = Len(num.ToString)
            While LenNum < 10
                stID = stID + "0"
                LenNum += 1
            End While
            stID = TextPlus + stID + num
        Catch ex As Exception
            stID = ""
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return stID
    End Function

    Public Function getCount(ByVal TableName As String, ByVal ColumnName As String) As Integer
        Dim number As Integer
        Dim cmd As New SqlCommand
        cmd.CommandText = "SELECT COUNT(" & ColumnName & ") FROM " & TableName & ""
        cmd.Connection = con
        Try
            con.Open()
            number = cmd.ExecuteScalar()
        Catch ex As Exception
            number = 0
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return number
    End Function

    Public Function getCountWithActive(ByVal TableName As String, ByVal ColumnName As String) As Integer
        Dim number As Integer
        Dim cmd As New SqlCommand
        cmd.CommandText = "SELECT COUNT(" & ColumnName & ") FROM " & TableName & " WHERE Active=1"
        cmd.Connection = con
        Try
            con.Open()
            number = cmd.ExecuteScalar()
        Catch ex As Exception
            number = 0
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return number
    End Function

End Class
